/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.tkgarment.system.dao;

import com.tkgarment.system.domain.Employee;
import com.tkgarment.system.factory.DAOFactory;
import com.tkgarment.system.inf.IDAO;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;

/**
 *
 * @author zraj
 */
public class EmployeeDAO extends AbstractDAO implements IDAO<Employee, String> {

    @Override
    public List<Employee> getAll() throws Exception {
        String sql = "select * from emp_master order by name_th ASC";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }
        List<Map<String,Object>> result = this.getCon().queryList(sql);
        List<Employee> emps = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Employee e = new Employee();
            Employee.mapfield(map,e);
            emps.add(e);
        }
        return emps;
    }

    @Override
    public int updateSingle(Employee t, String id) throws Exception {
        
        String pic = "";
        
        if (StringUtils.isNotEmpty(t.getPicPath())) {
           pic =  " pic_path = '"+  t.getPicPath() +"',";
        }
        
         String sql = "update emp_master set " +
"            emp_id = ?, status_id = ?, name_th = ?, surname_th = ?, name_en = ?, surname_en = ?, " +
"              birth_date = ?, sex_id = ?, emp_type_id = ?, marital_status_id = ?, " +
"            birth_place = ?, idcard_number = ?, idcard_start = ?, idcard_end = ?, blood_type_id = ?, " +
"             workstartdate = ?, work_regis_date = ?, contact_person = ?, " +
"            contact_tel = ?, graduate_level_id = ?, graduate_from = ?, graduate_year = ?, " +
"             email = ?, department_id = ?, title_id = ?, nickname = ?, religion_id = ?," +
"            birth_province = ?," + pic + " position_name = ?, company_tel = ?, " +
"             gpa = ?, graduate_major = ?, nationality_name = ? ,children = ?,insure_id = ?"
                 + ",bank_id = ?,married_id = ?,workend = ?  where emp_id = ?" ;
        
        return this.getCon().update(sql, t.getEmpId(),t.getStatusID(),t.getNameTH(),t.getSurnameTH(),t.getNameEN(),t.getSurnameEN(),
                t.getBirthDate(),t.getSexID(),t.getEmpTypeID(),t.getMaritalStatusID(),
                t.getBirthPlace(),t.getIdcardNumber(),t.getIdcardStartDate(),t.getIdcardEndDate(),t.getBloodTypeID(),
                t.getWorkStartDate(),t.getWorkRegisDate(),t.getContactPerson(),
                t.getContactTel(),t.getGraduateLevelID(),t.getGraduateFrom(),t.getGraduateYear(),
                t.getEmail(),t.getDepartmentID(),t.getTitleNameID(),t.getNickname(),t.getReligionID(),
                t.getBirthProvince(),t.getPositionName(),t.getCompanyTel(),
                t.getGpa(),t.getGraduateMajor(),t.getNationallity(),t.getChildren(),t.getInsureId(),t.getBankId(),t.getMarriedId(),t.getWorkEndDate(),id);
    }

    @Override
    public int deleteSingle(Employee t) throws Exception {
        String sql = "delete from emp_master where emp_id = ?";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }
        return this.getCon().update(sql, t.getEmpId());
    }

    @Override
    public int insertSingle(Employee t) throws Exception {
         String sql = "INSERT INTO emp_master(" +
"            emp_id, status_id, name_th, surname_th, name_en, surname_en, " +
"              birth_date, sex_id, emp_type_id, marital_status_id, " +
"            birth_place, idcard_number, idcard_start, idcard_end, blood_type_id, " +
"             workstartdate, work_regis_date, contact_person, " +
"            contact_tel, graduate_level_id, graduate_from, graduate_year, " +
"             email, department_id, title_id, nickname, religion_id," +
"            birth_province, pic_path, position_name, company_tel, " +
"             gpa, graduate_major, nationality_name,children,insure_id,bank_id,married_id,workend) " +
"    VALUES (?, ?, ?, ?, ?, ?, " +
"             ?, ?, ?, ?, " +
"            ?, ?, ?, ?, ?, " +
"             ?, ?, ?, " +
"            ?, ?, ?, ?," +
"             ?, ?, ?, ?, ?," +
"            ?, ?, ?, ?, " +
"            ?, ?, ?,?,?,?,?,?);";
        
        return this.getCon().add(sql, t.getEmpId(),t.getStatusID(),t.getNameTH(),t.getSurnameTH(),t.getNameEN(),t.getSurnameEN(),
                t.getBirthDate(),t.getSexID(),t.getEmpTypeID(),t.getMaritalStatusID(),
                t.getBirthPlace(),t.getIdcardNumber(),t.getIdcardStartDate(),t.getIdcardEndDate(),t.getBloodTypeID(),
                t.getWorkStartDate(),t.getWorkRegisDate(),t.getContactPerson(),
                t.getContactTel(),t.getGraduateLevelID(),t.getGraduateFrom(),t.getGraduateYear(),
                t.getEmail(),t.getDepartmentID(),t.getTitleNameID(),t.getNickname(),t.getReligionID(),
                t.getBirthProvince(),t.getPicPath(),t.getPositionName(),t.getCompanyTel(),
                t.getGpa(),t.getGraduateMajor(),t.getNationallity(),t.getChildren(),t.getInsureId(),t.getBankId(),t.getMarriedId(),t.getWorkEndDate());
    }

    public Employee getById(String empID)throws Exception {
         String sql = "select * from emp_master where emp_id = ?";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }
        
        Map<String,Object> map = this.getCon().querySingle(sql, empID);
      
            Employee e = new Employee();
            Employee.mapfield(map,e);
         
        return e;
    }

    public int updatePic(String empId, String filePath) {
        String sql = "update emp_master set pic_path = ? where emp_id = ?";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }
        return this.getCon().update(sql, filePath,empId);
    }

    public List<Employee> getByFilter(String searchField, String searchString, String searchOper)throws Exception {
       String sql = "select * from emp_master where 1 = 1 ";
       String searchF = "";
        if (searchField.compareTo("empId") == 0) {
            searchF = "emp_id";
        }
        if (searchField.compareTo("nameTH") == 0) {
            searchF = "name_th";
        }
        if (searchField.compareTo("surnameTH") == 0) {
            searchF = "surname_th";
        }
        if (searchField.compareTo("idcardNumber") == 0) {
            searchF = Employee.field_idcard_number;
        }
        if (searchField.compareTo("deptId") == 0) {
            searchF = Employee.field_department_id;
        }
       
       
        if (searchOper.compareTo("eq") == 0) {
            sql = sql + " and " + searchF + " = '" + searchString +"' ";
        }
        
        if (searchOper.compareTo("cn") == 0) {
            sql = sql + " and " + searchF + " like '%" + searchString +"%' ";
        }
       
               sql = sql + " order by name_th ASC";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }
        
        System.out.println(sql);
        
        List<Map<String,Object>> result = this.getCon().queryList(sql);
        List<Employee> emps = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Employee e = new Employee();
            Employee.mapfield(map,e);
            emps.add(e);
        }
        return emps;
    }
    
    
    
}
